package uk.ac.cam.echo.server.integrationtests;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import org.junit.After;
import org.junit.Before;

import uk.ac.cam.echo.server.Database;

/**
 * @author Tom Smith
 */
public class TestDatabase {

	protected Database m_db;
	protected Connection m_Connection;
	
	@Before
	/**
	 * Ran before all tests, creates a Database object with a connection, and required tables.
	 * @throws ClassNotFoundException The JDBC driver cannot be loaded.
	 * @throws SQLException
	 */
	public void setUp() throws ClassNotFoundException, SQLException {

		// Load the HSQLDB JDBC driver
		Class.forName("org.hsqldb.jdbc.JDBCDriver" );

		// Get the connection for the HSQLDB driver.
		this.m_Connection = DriverManager.getConnection("jdbc:hsqldb:file:./tests/integration/database", "SA", "");

		// Always write data out to disk.
		Statement delayStatement = this.m_Connection.createStatement();
		delayStatement.execute("SET WRITE_DELAY FALSE");
		delayStatement.close();
		
		this.m_Connection.setAutoCommit(false);

		this.m_db = Database.setConnection(this.m_Connection);
		
		this.createTables();
	}

	@After
	/**
	 * Ran after all tests, drops tables, closes the JDBC connection and throws the Database for GC.
	 * @throws SQLException
	 */
	public void tearDown() throws SQLException {
		this.dropTables();
		
		this.m_db = null;
	}

	/**
	 * Create the following tables on the connection.
	 * @throws SQLException
	 */
	private void createTables() throws SQLException {
		// List of CREATE TABLE SQL statements.
		LinkedList<String> tableCreation = new LinkedList<String>();
				
		tableCreation.add("CREATE TABLE " + Database.tblUser + "(RFID VARCHAR(64) NOT NULL, " + 
				"Forename VARCHAR(64), " +
				"Surname VARCHAR(64), " +
				"UserType VARCHAR(16) NOT NULL, " +
				"FacebookID VARCHAR(255) NOT NULL, " +
				"UNIQUE (FacebookID), PRIMARY KEY (RFID));");

		tableCreation.add("CREATE TABLE " + Database.tblFriend + "(FromUserID VARCHAR(64) NOT NULL, " +
				"ToUserID VARCHAR(64) NOT NULL, " +
				"PRIMARY KEY (FromUserID, ToUserID), " +
				"CONSTRAINT personA FOREIGN KEY (FromUserID) REFERENCES " + Database.tblUser + "(RFID), " +
				"CONSTRAINT personB FOREIGN KEY (ToUserID) REFERENCES " + Database.tblUser + "(RFID), " +
				"CONSTRAINT notMe CHECK (FromUserID <> ToUserID));");
		
		// IDENTITY makes LocationID primary key.
		tableCreation.add("CREATE TABLE " + Database.tblLocation + "(LocationRFID VARCHAR(64) NOT NULL, " +
				"LocationName VARCHAR(128), " +
				"PRIMARY KEY (LocationRFID), " +
				"UNIQUE (LocationName));");
		
		tableCreation.add("CREATE TABLE " + Database.tblAttraction + "(AttractionID INT NOT NULL IDENTITY, " +
				"LocationRFID VARCHAR(64), " +
				"AttractionName VARCHAR(128) NOT NULL, " +
				"AttractionType VARCHAR(32) NOT NULL, " +
				"UNIQUE (LocationRFID, AttractionName), " +
				"CONSTRAINT place FOREIGN KEY (LocationRFID) REFERENCES " + Database.tblLocation + "(LocationRFID));");
		
		tableCreation.add("CREATE TABLE " + Database.tblCheckIn + "(RFID VARCHAR(64) NOT NULL, " +
				"AttractionID INT NOT NULL, " +
				"Time DATETIME NOT NULL, " +
				"PRIMARY KEY (RFID, Time), " +
				"CONSTRAINT whoAttraction FOREIGN KEY (RFID) REFERENCES " + Database.tblUser + "(RFID), " +
				"CONSTRAINT checkIn FOREIGN KEY (AttractionID) REFERENCES " + Database.tblAttraction + "(AttractionID), " +
				"CONSTRAINT inPast CHECK (Time <= NOW()));");
		
		tableCreation.add("CREATE TABLE " + Database.tblSchedule + "(ScheduleID INT NOT NULL IDENTITY, " +
				"EventName VARCHAR(64) NOT NULL , " +
				"StartTime DATETIME NOT NULL, " +
				"EndTime DATETIME , " +
				"AttractionID INT NOT NULL, " +
				"Description VARCHAR(256), " +
				"CONSTRAINT stage FOREIGN KEY (AttractionID) REFERENCES " + Database.tblAttraction + "(AttractionID) , " +
				"CONSTRAINT length CHECK ((EndTime IS NULL) OR ((EndTime IS NOT NULL) AND (StartTime < EndTime))));");

		// Execute the SQL statements - create the tables.
		for (String table : tableCreation) {
			this.executeSQL(table);
		}
	}

	/**
	 * Drop the following tables on the connection.
	 * @throws SQLException
	 */
	private void dropTables() throws SQLException {
		// List of tables to DROP.
		LinkedList<String> tableDrop = new LinkedList<String>();
		tableDrop.add(Database.tblSchedule);
		tableDrop.add(Database.tblCheckIn);
		tableDrop.add(Database.tblAttraction);
		tableDrop.add(Database.tblLocation);
		tableDrop.add(Database.tblFriend);
		tableDrop.add(Database.tblUser);
		
		// Execute SQL statements - DROP the tables.
		for (String table : tableDrop) {
			this.executeSQL("DROP TABLE " + table + ";");
		}
	}

	/**
	 * Execute the SQL on the connection.
	 * @param sql any SQL statement to execute.
	 * @return true if the first result is a ResultSet object; false if it is an update count or there are no results 
	 * @throws SQLException
	 */
	private boolean executeSQL(String sql) throws SQLException {
		if (this.m_Connection == null) return false;
		
		// Execute SQL statement.
		Statement sqlStatement = this.m_Connection.createStatement();
		boolean result = sqlStatement.execute(sql);
		sqlStatement.close();
		
		// Commit changes to file, and release DB locks.
		this.m_Connection.commit();
		
		return result;
	}
	
}
